package cn.com.mydb;

import java.io.File;
import java.net.URISyntaxException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;

/**
 * 
 * @author hwb_work
 * @version 1.0
 */
public class DbManager {
	private static Log log = LogFactory.getLog(DbManager.class);
	
	private static Map<String, String> sqlMap  = LoadSqlMap();

	/**
	 * 获取数据库连接对象
	 * 
	 * @return
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException {
		return DBPool.getConnection();
	}

	
	/**
	 * 执行sql（select insert update delete）
	 * 
	 * @param map sqlId:paramMap
	 * @throws Exception
	 */
	public Map<String, Object> executeSql(Map<String, Map<String, Object>> map)
			throws Exception {
		Connection conn = null;
		int updateCount = 0;// 更新操作的个数，如果>1,则启动事务
		try {
			conn = getConnection();
			Map<String, Object> resMap = new HashMap<String, Object>();// 返回的数据
			for (Entry<String, Map<String, Object>> entry : map.entrySet()) {
				String sqlId = entry.getKey();
				String sql = getSql(sqlId);
				String action = sql.substring(0, 6);
				if ("select".equalsIgnoreCase(action)) {
					continue;
				} else if ("update".equalsIgnoreCase(action)
						|| "delete".equalsIgnoreCase(action)
						|| "insert".equalsIgnoreCase(action)) {
					updateCount++;
					if (updateCount > 1) {
						/* 启动事务 */
						log.info("----conn.beginTransaction()");
						conn.setAutoCommit(false);
						break;
					}
				} else {
					throw new SQLException("drop、create 等其他操作 暂时不允许执行");
				}
			}
			for (Entry<String, Map<String, Object>> entry : map.entrySet()) {
				String sqlId = entry.getKey();
				Map<String, Object> paramMap = entry.getValue();

				String sql = getSql(sqlId);
				List<String> paramsList = getSqlParamArray(sql);
				String currSql = formatSql(sql, paramMap);
				// select update delete insert drop create
				if (currSql.indexOf("\\$") != -1) {
					log.error("sql和提供的参数不匹配,sql:" + currSql);
					throw new SQLException("sql和提供的参数不匹配,sql:" + currSql);
				}
				String action = sql.substring(0, 6);
				if ("select".equalsIgnoreCase(action)) {
					List<Map<String, Object>> res = executeQuery(conn, currSql,
							paramsList, paramMap);
					resMap.put(sqlId, res);
				} else if ("update".equalsIgnoreCase(action)
						|| "delete".equalsIgnoreCase(action)
						|| "insert".equalsIgnoreCase(action)) {
					int res = executeUpdate(conn, currSql, paramsList, paramMap);
					resMap.put(sqlId, res);
				}

			}
			if (updateCount > 1) {
				/* 提交事务 */
				log.info("----conn.commit()");
				conn.commit();
			}
			return resMap;
		} catch (SQLException e) {
			if (conn != null && updateCount > 1) {
				log.error("----conn.rollback().execption");
				conn.rollback();
			}
			throw e;
		} finally {
			// TODO 测试阶段使用
			if(conn!=null) conn.close();
			// release();
		}
	}

	/**
	 * 获取sql中的参数(顺序)
	 * 
	 * @param sql 原始SQL ：select * from table1 where id=$ID$
	 * @return
	 */
	private List<String> getSqlParamArray(String sql) {
		String params[] = sql.split("\\$");
		List<String> list = new ArrayList<String>();
		for (int i = 1; i < params.length; i = i + 2) {
			list.add(params[i]);
		}
		return list;
	}

	/**
	 * 格式化SQL,将给的参数名称替换成占位符?
	 * 
	 * @param sql 原始sql
	 * @param paramMap 参数
	 * @return
	 */
	private String formatSql(String sql, Map<String, Object> paramMap) {
		for (Entry<String, Object> entry : paramMap.entrySet()) {
			// sql= sql.replaceAll("\\$[\\w]+\\$", "?");
			sql = sql.replaceAll("\\$[" + entry.getKey() + "]+\\$", "?");
		}
		return sql;
	}

	/**
	 * 通过通过sqlId，在配置文件中查询对应到SQL语句
	 * 
	 * @param sqlId (fileName.sqlId)
	 * @return
	 */
	public String getSql(String sqlId) {
		return sqlMap.get(sqlId);
	}

	/**
	 * 执行select操作
	 * 
	 * @param conn 数据库连接对象
	 * @param sql sql语句
	 * @param paramsList sql中对应的参数（有顺序的）
	 * @param paramMap sql中需要的参数<参数名称,参数值>
	 * @return 所有数据<行<列名,值>>
	 * @throws SQLException
	 */
	private List<Map<String, Object>> executeQuery(Connection conn,
			String sql, List<String> paramsList, Map<String, Object> paramMap)
			throws SQLException {
		/**
		 * 根据sqlId 从配置文件中取sql，将参数关键字通过正则，替换成占位符;//分页查询的操作不写入sql，重新封装sql，不同的数据库
		 * ，分页的方式不一样; 过滤首尾的空格，从第一个单词判断是哪种操作，如果有>1个的更新操作，则启动事务;
		 * 根据sqlId,从sqlId_paras中取参数，放到一个数组中，每个参数的位置和sql语句中相同名称参数的位置相同;
		 * 将参数放到sql中; 逐条执行; 将执行的结果再放到返回的Map中，key=sqlId; //TODO 能否返回查询的的字段名称
		 * ArrayList<ArrayList<Map<ColumnName,value>>> res;
		 * 结果集<一行数据<一个字段的数据<字段名称,对应字段的值>>>
		 * 
		 * TODO hibernate的三级缓存是怎么实现的？
		 */
		PreparedStatement stm = conn.prepareStatement(sql);
		List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
		for (int i = 0; i < paramsList.size(); i++) {
			stm.setObject(i + 1, paramMap.get(paramsList.get(i)));
		}
		//打印sql
		getPreparedSQL(sql, paramsList, paramMap);
		
		ResultSet rs = stm.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();// rs为查询结果集
		int columnCount = rsmd.getColumnCount();
		while (rs.next()) {
			Map<String, Object> row = new HashMap<String, Object>();
			for (int i = 1; i <= columnCount; ++i) {
				String name = rsmd.getColumnName(i).toLowerCase();
				Object value = rs.getObject(i);
				row.put(name, value);
			}
			rows.add(row);
		}
		return rows;
	}

	/**
	 * 执行update Map<sqlId,Map<paraName,paraValue>> Map<sqlId,Result>
	 * 
	 * @param list
	 * @return
	 * @throws Exception
	 */
	private int executeUpdate(Connection conn, String sql,
			List<String> paramsList, Map<String, Object> paramMap)
			throws Exception {
		PreparedStatement stm = conn.prepareStatement(sql);
		for (int i = 0; i < paramsList.size(); i++) {
			if (paramsList.get(i) == null) {
				log.error("----sql:" + sql + "--中包含有为null的参数……");
				throw new Exception("参数名称不能为空！");
			}
			// TODO 需要对Date类型做处理，不同的数据库类型不一样
			stm.setObject(i + 1, paramMap.get(paramsList.get(i)));
		}
		getPreparedSQL(sql, paramsList, paramMap);
		return stm.executeUpdate();
	}

	/**
	 * 得到最终执行的SQL语句
	 * 
	 * @param sql
	 * @param paramsList
	 * @param paramMap
	 * @return
	 */
	private String getPreparedSQL(String sql, List<String> paramsList,
			Map<String, Object> paramMap) {
		StringBuffer reSql = new StringBuffer();
		String[] subSql = sql.split("\\?");
		int paramCount = subSql.length - 1;
		if (1 > paramCount) {
			return sql;
		} else if (paramsList == null || paramsList.contains(null)
				|| paramCount != paramsList.size()) {
			log.error("----sql:" + sql
					+ "--中包含有为null的参数，或者sql中的参数个数和提供的参数个数不相等……");
		} else {
			reSql.append(subSql[0]);
			for (int i = 0; i < paramsList.size(); i++) {
				reSql.append("'").append(paramMap.get(paramsList.get(i)))
						.append("'").append(subSql[i + 1]);
			}
		}
		log.info("SQL:" + reSql.toString());
		return reSql.toString();
	}

	public static void main2(String[] args) {
		try {
			String sqlId0 = "sql0";
			Map<String, Object> paramMap0 = new HashMap<String, Object>();
			paramMap0.put("roleid", "000");

			String sqlId1 = "sql1";
			Map<String, Object> paramMap1 = new HashMap<String, Object>();
			paramMap1.put("te_name", "名字1");
			paramMap1.put("operId", "dxgl");

			String sqlId2 = "sql2";
			Map<String, Object> paramMap2 = new HashMap<String, Object>();
			paramMap2.put("te_name", "名字2");
			paramMap2.put("operId", "dongjl");
			Map<String, Map<String, Object>> map = new HashMap<String, Map<String, Object>>();
			map.put(sqlId0, paramMap0);
			map.put(sqlId1, paramMap1);
			map.put(sqlId2, paramMap2);
			Map<String, Object> res = new DbManager().executeSql(map);
			List<Map<String, Object>> res0 = (List<Map<String, Object>>) res.get(sqlId0);
			int res1 = (Integer) res.get(sqlId1);
			int res2 = (Integer) res.get(sqlId2);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private List<File> getFiles(List<File> fileList, File file){
		if(file!=null){
			if(file.isDirectory()){
				File[] files = file.listFiles();
				for (File file2 : files) {
					getFiles(fileList,file2);
				}
			}else{
				fileList.add(file);
			}
		}else{
			//null
		}
		return fileList;
	}
	
	/**
	 * 加载sql文件
	 * @return Map filename.id：sql
	 */
	public static Map<String, String> LoadSqlMap(){
		try {
			sqlMap = new DbManager().execLoadSqlMap();
		} catch (DocumentException e) {
			log.error("初始化sql文件异常："+e);
			e.printStackTrace();
		} catch (URISyntaxException e) {
			log.error("获取sql文件异常:"+e);
			e.printStackTrace();
		}
		return sqlMap;
	}
	
	/**
	 * 加载sql文件
	 * @return Map filename.id：sql
	 * @throws DocumentException
	 * @throws URISyntaxException
	 */
	private Map<String, String> execLoadSqlMap() throws DocumentException, URISyntaxException {
		sqlMap = null; 
		ClassLoader cl = this.getClass().getClassLoader();
		String path = cl.getResource("sql").toURI().getPath();
		log.debug("sql.path:" + path);
		File file = new File(path);
		List<File> fileList  = new ArrayList<File>();
		fileList = getFiles(fileList,file);
		if(fileList != null){
			sqlMap = new HashMap<String, String>();
			for (File temp_file : fileList) {
				String file_name = temp_file.getName();
				
				log.debug("sql.file:"+file_name);
				
				file_name = file_name.substring(0, file_name.lastIndexOf("."));
				SAXReader reader = new SAXReader();
				Document doc = reader.read(temp_file);
				Element root = doc.getRootElement();
				for (int i = 0; i < root.nodeCount(); i++) {
					Node node = root.node(i);
					if (node instanceof Element) {
						String id = ((Element) node).attributeValue("id");
						String sql = node.getText().trim();
						String sqlId = file_name+"."+id;
						if(sqlMap.containsKey(sqlId)){
							throw new DocumentException("发现重复的sql_id:"+sqlId);
						}
						sqlMap.put(sqlId, sql);
					}
				}
			}
		}else{
			//sql文件目录不存在
		}
		return sqlMap;
	}
}
